package com.cse.p2p.database;

import java.sql.*;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 *
 * @author DUCMINHKHOI
 */
public class ServerDatabase {

    private static final String DATABASE_URL = "jdbc:sqlite:server.db";
    private static boolean isDatabaseOpen = false;
    private static Connection connection = null;
    private static Statement statement = null;
    private static ResultSet resultSet = null;

     /**
     * This method will open the Database client.db
     * consist of open connection, statement
     * This method is called only once at the beginning of the client's life
     * @throws SQLException
     * @throws Exception
     *
     */
    public static void openDatabase() throws SQLException, Exception{
        if (!isDatabaseOpen){
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection(DATABASE_URL);
            statement = connection.createStatement();
            isDatabaseOpen = true;
        }
    }

    /**
     * This method with close the Database
     * This method is called once at the end of the client's life
     */
    public static void closeDatabase(){
        try {
            connection.close();
        } catch (Exception e) {
        }

        isDatabaseOpen = false;
    }

    private static void closeResultSetAndStatment() {
        try {
            resultSet.close();
            statement.close();
        } catch (Exception e) {
        }
    }

    /**
     * This method will check whether the given user exist in User table
     * Using when User login to File Sharing network
     * @param username: username
     * @return true/false: user exist or not
     * @throws SQLException
     */
    public static boolean  authenticateUser(String username) throws SQLException {
        boolean authenticated = false;
        resultSet = statement.executeQuery("select * from user where username ='" + username + "';");
        if (resultSet.next()){
            authenticated = true;
        }
        closeResultSetAndStatment();
        return authenticated;
    }

    /**
     * This method will receive the IPAddress of a given username
     * @param username
     * @return ipAddress: String
     * @throws SQLException
     */
    public static String getUserIP(String username) throws SQLException{
        String ipAddress = null;
        resultSet = statement.executeQuery("select * from user where username ='" + username + "';");
        if (resultSet.next()){
            ipAddress = resultSet.getString("ipaddress");
        }
        return ipAddress;

    }

    /**
     * this method will set the column receiveDicFile in table user of a given username
     * Using when when Server create the DicFile but the user not receive because the corrupt
     * @param username: username
     * @param value: True/false the value true or false you want to set
     * @throws SQLException
     */
    public static void setUserReceiveDicFileYet(String username, int value) throws SQLException {
        statement.executeUpdate("update user set dicfilereceive = "+ value + " where username = '" + username + "';");
        closeResultSetAndStatment();
    }

    /**
     * This method will check whether the User receive the DicFile from Server (in case the connection corrupt)
     * Using when a user login to sharing network and check this user have DicFile wait to download or not
     * @return true/false: the value of receiveDicFile column in the user username
     */
    public static boolean  getUserReceiveDicFileYet(String username) throws SQLException {
        boolean receive = false;

        resultSet = statement.executeQuery("select * from user where username = '" + username + "';");
        if (resultSet.next()){
            receive = resultSet.getBoolean("dicfilereceive");
        }
        closeResultSetAndStatment();
        return receive;
    }

    /**
     * This method will insert new row in File table and return the id of this row
     * Using when Client upload a file to Server completely
     * @param fileName: file's name
     * @param fileRealName: file's real name
     * @param fileSize: file's Size
     * @param filePath: file's Path (given by localFile)
     * @param fileDescribe: file's Describe
     * @param clientUpIP: clientUP IP
     * @return the ID of the file in file table
     * @throws SQLException
     */
    public static int createNewFileAndReturnFileID(String fileName, String fileRealName,
            long fileSize, String filePath, String fileDescribe, String clientUpIP) throws SQLException {

        int number = -1;
        statement.executeUpdate(String.format("insert into file(fileName, fileRealName, fileSize, filePath, fileDescribe, clientUpIP) values('%s','%s', %d, '%s', '%s', '%s');commit;",
                fileName, fileRealName, fileSize, filePath, fileDescribe, clientUpIP));
        statement.close();

        String sq = String.format("select * from file where filename = '%s' and"
                + " filerealname = '%s' and  filesize = %d and  filepath='%s' and filedescribe='%s' and clientupip ='%s';",
                fileName, fileRealName, fileSize, filePath, fileDescribe, clientUpIP);

        resultSet = statement.executeQuery(sq);

        if (resultSet.next()){
            number = resultSet.getInt("id");
        }
        closeResultSetAndStatment();
        return number;
    }

    /**
     * This method will return the path of a given file's ID from file table
     * @param fileID: file's ID
     * @return filePath: String of filePath
     * @throws SQLException
     */
    public static String getFilePath(int fileID) throws  SQLException{
        String filePath = null;
        resultSet = statement.executeQuery("select * from file where id = " + fileID +";");
        if (resultSet.next()){
            filePath = resultSet.getString("filepath");
        }
        closeResultSetAndStatment();
        return filePath;
    }

    /**
     * This method will return the fileName of given file's ID from file table
     * @param fileID: file's ID
     * @return fileName: String of fileName
     * @throws SQLException
     */
    public static String getFileName(int fileID) throws  SQLException{
        String fileName = null;
        resultSet = statement.executeQuery("select * from file where id = " + fileID +";");
        if (resultSet.next()){
            fileName = resultSet.getString("filename");
        }
        closeResultSetAndStatment();
        return fileName;
    }

    /**
     * This method will return the fileSize of given file's ID from file table
     * @param fileID: file's ID
     * @return fileSize: Long type of fileSize
     * @throws SQLException
     */
    public static long getFileSize(int fileID) throws  SQLException{
        long fileSize = 0;
        resultSet = statement.executeQuery("select * from file where id = " + fileID +";");
        if (resultSet.next()){
            fileSize = resultSet.getLong("filesize");
        }
        closeResultSetAndStatment();
        return fileSize;
    }

    /**
     * This method will count the filename exist in the file table
     * Using to create the file real name exactly: filerealname = filename + ordernumber
     * @param fileName: file's name
     * @return count: the number of this file's name
     * @throws SQLException
     */

    public static int countSameFileName(String fileName) throws SQLException{
        int count = 0;
        resultSet = statement.executeQuery("select count(*) as number from file where filename = '" + fileName + "';");
        if (resultSet.next()){
            count = resultSet.getInt("number");
        }
        return count;
    }

    /**
     * This method will return the fileDescribe of given file's ID from the file table
     * @param fileID: file's ID
     * @return fileDescribe: String of describe
     * @throws SQLException
     */
    public static String getFileDescribe(int fileID) throws  SQLException{
        String fileDescribe = null;
        resultSet = statement.executeQuery("select * from file where id = " + fileID +";");
        if (resultSet.next()){
            fileDescribe = resultSet.getString("filedescribe");
        }
        closeResultSetAndStatment();
        return fileDescribe;
    }

    /**
     * This method will return the fileRealName of a given file's ID from file table
     * @param fileID: file's ID
     * @return fileRealName: String of file real name
     * @throws SQLException
     */
    public static String getFileRealName(int fileID) throws  SQLException{
        String fileRealName = null;
        resultSet = statement.executeQuery("select * from file where id = " + fileID +";");
        if (resultSet.next()){
            fileRealName = resultSet.getString("filerealname");
        }
        closeResultSetAndStatment();
        return fileRealName;
    }

    /**
     * This method will return the ClientUPIP of a given file's ID from file table
     * @param fileID: file's ID
     * @return clientUpIP: String of IP of client upload this file to Server
     * @throws SQLException
     */
    public static String getClientUpIP(int fileID) throws  SQLException{
        String clientUpIP = null;
        resultSet = statement.executeQuery("select * from file where id = " + fileID +";");
        if (resultSet.next()){
            clientUpIP = resultSet.getString("clientupip");
        }
        closeResultSetAndStatment();
        return clientUpIP;
    }

}
